package us.bighelp2.marc.common;

public class Queries 
{
	public static final String SQL_GATHER_APPLICATION_SETTINGS = 
		"SELECT a.key, a.value, a.userviewable FROM sys_settings AS a";
	public static final String SQL_GATHER_AVAILABLE_MODULES = 
		"SELECT moduleName, moduleUrl, modulebody FROM sys_modules";
	
	public static final String SQL_GATHER_ALL_USERS = 
		"SELECT * FROM person ORDER BY LName ASC";
	public static final String SQL_GATHER_KNOWN_ALIASES = 
		"SELECT * FROM alias WHERE UserID LIKE ?";
	public static final String SQL_FIND_USER = 
		"SELECT * FROM person WHERE UserID = ?";
	public static final String SQL_AUTHENTICATE_USER = 
		"SELECT "+
			"r.alias "+
			",p.password "+
		"FROM "+
			"person AS p "+
			"INNER JOIN "+
			"alias AS r "+
			"ON "+
			"r.personid = p.personid "+
			"AND "+
			"r.alias = ?";
	
	public static final String SQL_GATHER_ACTIVE_ALIASES = 
		"SELECT "+
			"als.UserID AS 'UserID' "+
			",als.Alias AS 'Alias' "+
			",usr.Pref_Name AS 'Pref_Name' "+
			",usr.LName AS 'LName' "+
			",usr.Picture AS 'Picture' "+
		"FROM "+
			"alias AS als "+
			"INNER JOIN "+
				"users AS usr "+
				"ON "+
				"usr.UserId = als.UserID "+
		"WHERE "+
			"als.Active IS TRUE "+
			"AND "+
			"usr.Access_Level <> 'Inactive' "+
		"ORDER BY LName ASC ";
	public static final String SQL_GATHER_ACTIVE_USERS = 
		"SELECT "+
			"usr.UserId AS 'UserID' "+
			",usr.UserId AS 'Alias' "+
			",usr.Pref_Name AS 'Pref_Name' "+
			",usr.LName AS 'LName' "+
			",usr.Picture AS 'Picture' "+
		"FROM "+
			"users AS usr "+
		"WHERE "+
			"usr.Access_Level <> 'Inactive' "+
		"ORDER BY LName ASC ";
	public static final String SQL_GATHER_ACTIVE_ADMINS = 
		"SELECT "+
			"usr.UserId AS 'UserID' "+
			",usr.UserId AS 'Alias' "+
			",usr.Pref_Name AS 'Pref_Name' "+
			",usr.LName AS 'LName' "+
			",usr.Picture AS 'Picture' "+
		"FROM "+
			"users AS usr "+
		"WHERE "+
			"usr.Access_Level LIKE 'Administrator' " +
		"ORDER BY LName ASC ";
	public static final String SQL_TOP_6_MTGS = 
		"CREATE TEMPORARY TABLE mtgs AS "+
		"SELECT "+
			"mtg.MeetingID "+
			",mtg.StartDate "+
		"FROM "+
			"meetings AS mtg "+
		"WHERE "+
			"mtg.MeetingType = 1 "+
			"AND "+
			"mtg.EndDate IS NOT NULL "+
		"ORDER BY "+
			"mtg.StartDate DESC "+
		"LIMIT "+
			"10; ";
	public static final String SQL_TOP_6_MTGS_ATTENDANCE = 
		"SELECT "+
			"mtgs.MeetingID "+
			",DATE(mtgs.StartDate) AS StartDate "+
			",COUNT(atn.AliasUsed) AS Attendees "+
		"FROM "+
			"attendance AS atn "+
			"INNER JOIN "+
				"mtgs "+
				"ON "+
				"mtgs.MeetingID = atn.MeetingID "+
		"GROUP BY "+
			"mtgs.MeetingID ";
	
	public static final String SQL_GET_MTG_TYPES = 
		"SELECT * FROM meetingtypes";
	
	public static final String SQL_DROP_MTGS_TABLE = 
		"DROP TEMPORARY TABLE IF EXISTS mtgs";
	public static final String SQL_CHECK_FOR_USERID = 
		"SELECT * FROM users WHERE LOWER(users.UserId) LIKE ?;";
	public static final String SQL_NEW_USER = 
		"INSERT INTO users (UserId, FName, MInitial, LName, Pref_Name, DOB, Gender, Address, City, Zip, Phone, Email, Shirt, "+ 
				"School, Grade, Highest_Sacrament, Confirmation, Access_Level, InsuranceCo, InsuranceNum, Medications, Allergies, " +
				"MedicalIssues, PermissionToTreat, EmgContact, EmgRelation, EmgNumber, ReportsTo, Picture) "+
			"VALUES ( "+
				"?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
	public static final String SQL_CREATE_UPDATE_RECORD =
		"INSERT INTO " +
			"au_users (UserId, `Timestamp`, UpdateType, OldValue, NewValue, EffectiveDate) " +
		"VALUES (?,?,?,?,?,?)";
	public static final String SQL_CREATE_NEW_MEETING = 
		"INSERT INTO "+
			"meetings (MeetingType, StartDate, EndDate, StartedBy, EndedBy, AttendantMode) "+
		"VALUES "+
			"(?,NOW(),NULL,?,NULL,?);";
	public static final String SQL_GET_CURRENT_MTG_ID =
		"SELECT "+
			"MeetingID "+
		"FROM "+
			"meetings "+
		"ORDER BY "+
			"MeetingID DESC "+
		"LIMIT 1;";	
	public static final String SQL_GET_OPEN_MTGS = 
		"SELECT "+
			"mtg.MeetingID "+
			",mt.MeetingTypeName "+
			",mtg.StartedBy " +
			",mtg.AttendantMode "+
		"FROM "+
			"meetings AS mtg "+
			"INNER JOIN "+
				"meetingtypes AS mt "+
				"ON "+
				"mt.MeetingTypeID = mtg.MeetingType "+
		"WHERE "+
			"EndDate IS NULL";
	public static final String SQL_SIGN_IN_USER = 
		"INSERT INTO "+
			"attendance(MeetingID, AliasUsed, EntryTime) "+
		"VALUES "+
			"(?, ?, CURTIME())";
	public static final String SQL_CLOSE_MEETING =
		"UPDATE "+
			"meetings "+
		"SET "+
			"EndDate = NOW(), "+
			"EndedBy = ? "+
		"WHERE "+
			"MeetingID = ?";
	public static final String SQL_GATHER_SIGNED_IN_USERS = 
		"SELECT "+
			"als.UserID "+
		"FROM "+
			"attendance AS atn "+
			"INNER JOIN "+
				"alias AS als "+
				"ON "+
				"als.Alias = atn.AliasUsed "+
				"OR "+
				"als.UserID = atn.AliasUsed "+
		"WHERE "+
			"MeetingID = ? ";
	public static final String SQL_ASSOCIATE_NEW_ALIAS = 
		"INSERT INTO "+
			"alias(UserID, Alias, AliasType, Active) "+
		"VALUES "+
			"(?, ?, ?, ?);";
	public static final String SQL_GATHER_ACTIVE_CARRIERS = 
		"SELECT * FROM text_companies WHERE active = 1";
}
